<?php
/**
* @file $Id: database.inc.php 484 2007-05-08 14:34:56Z focus-sis $
* @package Focus/SIS
* @copyright Copyright (C) 2006 Andrew Schmadeke. All rights reserved.
* @license http://www.gnu.org/copyleft/gpl.html GNU/GPL, see LICENSE.txt
* Focus/SIS is free software. This version may have been modified pursuant
* to the GNU General Public License, and as distributed it includes or
* is derivative of works licensed under the GNU General Public License or
* other free or open source software licenses.
* See COPYRIGHT.txt for copyright notices and details.
*/

// Establish DB connection.
function db_start()
{	global $DatabaseServer
	,      $DatabaseUsername
	,      $DatabasePassword
	,      $DatabaseName
	,      $DatabasePort
	,      $DatabaseType
	,      $connection
	;
	
	switch ($DatabaseType) {
	case 'postgres':
		if (!$connection) {
			if ($DatabaseServer != 'localhost') {
				$connectstring = "host=$DatabaseServer";
			}
			
			$connectstring // .= 
					.= " port=$DatabasePort"
					.  " dbname=$DatabaseName"
					.  " user=$DatabaseUsername"
					;
					
			if(!empty($DatabasePassword)) {
				$connectstring .= " password=$DatabasePassword";
			}
			
			$connection = pg_connect($connectstring);
			
			if(!$connection){
				if ($DatabaseServer != 'localhost') {
					$connectstring // = 
							= "host=$DatabaseServer"
							. " port=$DatabasePort"
							;
				}
				
				$connectstring // .= 
							.= " dbname=$DatabaseName"
							.  " user=$DatabaseUsername"
							;
							
				if (!empty($DatabasePassword)) {
					$connectstring .= " password=$DatabasePassword";
				}
				
				$connection = pg_connect($connectstring);
				
				if (!$connection) {
					$connectstring // = 
							= "host=$DatabaseServer"
							. " port=$DatabasePort"
							. " dbname=$DatabaseName"
							. " user=$DatabaseUsername"
							;
							
					if(!empty($DatabasePassword)) {
						$connectstring.=" password=$DatabasePassword";
					}
					
					$connection = pg_connect($connectstring);
				}
			}
		}
		break;

	case 'oracle':
		$connection = @ocilogon
				( $DatabaseUsername
				, $DatabasePassword
				, $DatabaseServer
				);
				
		break;		

	default:
		//you should complain here
		break;
	}
	
	// Error code for both.
	if ($connection === false) {
		switch ($DatabaseType) {
		case 'postgres':
			$errormessage = pg_last_error($connection);
			break;

		case 'oracle':
			$errors = OciError();
			$errormessage = $errors['message'];
			break;

		default:
			$errormessage = "Unsupported database type: $DatabaseType";
			break;
		}
		
		echo '<H2>I\'m sorry, but it looks like our database is down.</H2>
				<font color="gray">  
					Please contact the system administrator.
				</font>';
		exit;
	}
	return $connection;
}

// This function connects, and does the passed query, then returns a connection identifier.
// Not receiving the return == unusable search.
//		ie, $processable_results = DBQuery("select * from students");
function DBQuery($sql)
{	global $DatabaseType
	,      $_FOCUS
	,      $IgnoreFiles
	,      $FocusPath
	,      $staticpath
	,      $sql_data
	,      $PluginPath
	,      $sql_array
	;

	$connection = db_start();

	if ($PluginPath) {
		if (file_exists($PluginPath . '/plugin_engine.php')) {
			include($PluginPath . '/plugin_engine.php');
		}
	}
			
	switch ($DatabaseType) {
	case 'postgres':
		$sql = ereg_replace("([,\(=])[\r\n\t ]*''", '\\1NULL', $sql);
		$sql_array[] = $sql;
		$result = @pg_exec($connection, $sql);
		if ($result === false) {
			$last_error = pg_last_error($connection); 
			LogSql("$sql\nQuery Failed: $last_error\n\n\n");
			db_show_error($sql, "DB Execute Failed.", $last_error);
		}
		else {
			LogSql("$sql\nReturn Code: ".pg_result_status($result, PGSQL_STATUS_STRING)."\n"
					.'Affected Rows: '.pg_affected_rows($result)."\n"
					.'Selected Rows: '.pg_num_rows($result)."\n\n\n"
					);
		}
		break;

	case 'oracle':
		$result = @ociparse($connection, $sql);
		if ($result === false) {
			$errors = OCIError($connection);
			db_show_error($sql,"DB Parse Failed.", $errors['message']);
		}
		if (!@OciExecute($result)) {
			$errors = OCIError($result);
			db_show_error($sql,"DB Execute Failed.", $errors['message']);
		}
		OciCommit($connection);
		OciLogoff($connection);
		break;
		
	default:
		db_show_error("Unsupported database type: '$DatabaseType'");
		break;
	}
	
	return $result;
}

function DBDate()
{	global $DatabaseType;

	switch ($DatabaseType) {
		case 'postgres':	return date('Y-m-d');
		case 'oracle':		return strtoupper(date('d-M-y'));
		default:			die("Unsupported database type: '$DatabaseType'"); 
	}
}

// return next row.
function db_fetch_row($result, $change_keys_to_upper=true)
{	global $DatabaseType;
	
	switch ($DatabaseType) {
	case 'postgres':
		if ($change_keys_to_upper == true) {
			return @array_change_key_case(
					pg_fetch_array($result, NULL, PGSQL_ASSOC)
					,CASE_UPPER);
		}
		else {
			return pg_fetch_array($result, NULL, PGSQL_ASSOC);
		}
		break;

	case 'oracle':
		OCIFetchInto($result, $row, OCI_ASSOC + OCI_RETURN_NULLS);
		if ($change_keys_to_upper == true) {
			return @array_change_key_case($row, CASE_UPPER);
		}
		else {
			return $row;
		}
		break;

	default:
		db_show_error("Unsupported database type: '$DatabaseType'");
		break;
	}
}


function DBGet($QI, $functions=array(), $index=array())
{	global $THIS_RET;

	$index_count = count($index);
	$tmp_THIS_RET = $THIS_RET;
	$results = array();
	
	while ($RET = db_fetch_row($QI)) {
		$THIS_RET = $RET;
		
		if ($index_count) {
			$ind = '';
			foreach ($index as $col) {
				$ind .= "['".str_replace("'", "\'", $THIS_RET[$col])."']";
			}
			eval('$s'.$ind.'++;$this_ind=$s'.$ind.';');
		}
		else {
			$s++; // 1-based if no index specified
		}
		
		foreach ($RET as $key=>$value) {
			if ($functions[$key] 
			&& function_exists($functions[$key])
			) {
				if ($index_count) {
					eval('$results'
							. $ind
							. '[$this_ind][$key] ='
							. ' $functions[$key]($value,$key);');
				}
				else {
					$results[$s][$key] = $functions[$key]($value,$key);
				}
			}
			else {
				if ($index_count) {
					eval('$results' . $ind . '[$this_ind][$key] = $value;');
				}
				else {
					$results[$s][$key] = $value;
				}
			}
		}
	}

	$THIS_RET = $tmp_THIS_RET;

	return $results;
}

// runs a query that is expecting a single row, helps avoid $RET[1]['FIELD']
function DBGetRow($sql, $functions=array())
{
	$results = DBGet(DbQuery($sql), $functions);
	return $results[1];
}

// runs a query that expects to return a single row and single column (scalar return)
// it also expects the value sought to be called 'VALUE' as in
// select count(x) AS VALUE
function DBGetValue($sql)
{
	$results = DBGet(DbQuery($sql));
	return $results[1]['VALUE'];
}

// creates and executes a sql update statement in the form of:
// update $tablename 
// set $nonkey_column_keys = $nonkey_column_values
// where $key_column_keys = $key_column_values
// notice the key columns are the first parameter, becuase they are more important
// but, in the update sql statement they would be in the where clause and so 
// they would come after.
//   does all the wrapping and escape sequencing of values for you
function DBUpdate($tablename, $key_columns, $nonkey_columns) {
	DBQuery(makeUpdateSql($tablename, $key_columns, $nonkey_columns));
}

// creates and executes a sql insert statement in the form of:
// insert into $tablename 
// ($column__value_keys) VALUES ($column_values)
//   does all the wrapping and escape sequencing of values for you
function DBInsertInto($tablename, $column_values) {
	DBQuery(makeInsertSql($tablename, $column_values));
}

// performs either an insert or an update statement based on whether or
//  not their is a column already that has the key_column values
function DBUpdateOrInsert($tablename, $key_columns, $nonkey_columns) {
	if (recordExists($tablename, $key_columns)) {
		DBQuery(makeUpdateSql($tablename, $key_columns, $nonkey_columns));
		//can we return an error if it didnt update?
	}
	else {
		DBQuery(makeInsertSql($tablename, $key_columns, $nonkey_columns));
		//can we return an error if it didnt update?
	}
}


you are here

function recordExists($tablename, $key_columns) {
	foreach ($key_columns as $column_name => $value) {
		$key_fields .= "
 ,      $column_name";
		if (substr($value, 0, 2) == '# ') {
			$value = substr($value, 2);  //trim the "# "
			if (strtoupper($value) == 'NULL') {
				$key_fields .= " IS NULL";
			}
			else {
				$key_fields .= " = $value";
			}
		}
		else {
			$key_fields .= " = " . DBEncode($value);
		}
	}
	// trim out that initial CR + LF + comma (if it is just a LF w/no CR, it will pull a leading space, which is ok
	$key_fields = substr($key_fields, 1);
	
	$cnt = DBGetValue("
		SELECT count(*) as VALUE
		FROM   $tablename
		WHERE  $key_fields
		");
		
	if ($cnt == 0) {
		return false;
	}
	elseif ($cnt > 1) {
		echo "<!-- recordExists function counted $cnt records in $tablename where $keyfields this function should only be used for seeking primary key combinations!!! -->";
		return true;
	}
	else {
		return true;
	}
}

//I allow for more_column_values so people can make key_columns and update_columns, and pass them to makeInsert or makeUpdate
function makeInsertSql($tablename, $column_values ,$more_column_values) {
	// if they sent us two sets of column values, merge them into one big list
	if ($more_column_values) {
		$column_values = array_merge($more_column_values, $column_values);
	}
	
	foreach ($column_values as $column_name => $value) {
		$columns .= "
 ,  $column_name";
			 
		if (!is_array($value)
		&&  substr($value, 0, 2) == '# '
		){
			$values .= "
 ,  " . substr($value, 2);
		}
		else {
				$values .= "
 ,  " . DBEscape($value);
		}
	}				
	// trim out that initial CR + LF + comma (if it is just a LF w/no CR, it will pull a leading space, which is ok
	$values = substr($values, 1);
	
	return "
INSERT INTO  $tablename
( $columns 
) VALUES
( $values 
)";

}

function makeUpdateSql($tablename, $key_columns, $update_columns) {
	foreach ($key_columns as $column_name => $value) {
		$key_fields .= "
 ,      $column_name";
		if (substr($value, 0, 2) == '# ') {
			$value = substr($value, 2);  //trim the "# "
			if (strtoupper($value) == 'NULL') {
				$key_fields .= " IS NULL";
			}
			else {
				$key_fields .= " = $value";
			}
		}
		else {
			$key_fields .= " = " . DBEncode($value);
		}
	}
	// trim out that initial CR + LF + comma (if it is just a LF w/no CR, it will pull a leading space, which is ok
	$key_fields = substr($key_fields, 4);
	
	foreach ($update_columns as $column_name => $value) {
		if (!is_array($value)) {
			$update_fields .= "
 ,      $column_name =";
			if (substr($value, 0, 2) == '# ') {
				$update_fields .= substr($value, 1);  //trim the #
			}
			else {
				$update_fields .= " " . DBEncode($value);
			}
		}
		else {
			$update_fields .= "
 ,      $column_name = '||";
 
			foreach ($value as $val) {
				if ($val) {
					$update_fields .= str_replace("\'","''"
						  , str_replace('``', '"', $val)) 
						  . '||';
				}
			}
			$update_fields .= "'";
		}
	}				
	// trim out that initial CR + LF + comma (if it is just a LF w/no CR, it will pull a leading space, which is ok
	$update_fields = substr($update_fields, 4);
	
	return "
UPDATE  $tablename
SET     $update_fields
WHERE   $key_fields";
}


// returns code to go into SQL statement for accessing the next value of a sequenc	function db_seq_nextval($seqname)
function db_seq_nextval($seqname)
{	global $DatabaseType;

	if(strtolower($DatabaseType)=='oracle')
		$seq=$seqname.".nextval";
	elseif(strtolower($DatabaseType)=='postgres')
		$seq="nextval('".$seqname."')";

	return $seq;
}

// start transaction
function db_trans_start($connection)
{	global $DatabaseType;
	
	if(strtolower($DatabaseType)=="postres") 
		db_trans_query($connection,"BEGIN WORK");
}

// run query on transaction -- if failure, runs rollback.
function db_trans_query($connection,$sql) 
{	global $DatabaseType;

	if(strtolower($DatabaseType)=="oracle") 
	{
		$parse = ociparse($connection,$sql);
		if($parse===false)
		{
			db_trans_rollback($connection);
			db_show_error($sql,"DB Transaction Parse Failed.");
		}
		$result=OciExecute($parse,OCI_DEFAULT);
		if ($result===false) 
		{
			db_trans_rollback($connection);
			db_show_error($sql,"DB Transaction Execute Failed.");
		}
		$result=$parse;
	} 
	elseif(strtolower($DatabaseType)=="postgres") 
	{
		$sql = ereg_replace("([,\(=])[\r\n\t ]*''",'\\1NULL',$sql);
		$result = pg_query($connection,$sql);
		if($result===false) 
		{
			db_trans_rollback($connection);
			db_show_error($sql,"DB Transaction Execute Failed.");
		}
	}

	return $result;
}

// rollback commands.
function db_trans_rollback($connection) 
{	global $DatabaseType;

	if (strtolower($DatabaseType)=="oracle")
		OCIRollback($connection);
	elseif(strtolower($DatabaseType)=="postgres")
		pg_query($connection,"ROLLBACK");
}

// commit changes.
function db_trans_commit($connection) 
{	global $DatabaseType;

	if(strtolower($DatabaseType)=="oracle")
		OCICommit($connection);
	elseif(strtolower($DatabaseType)=="postgres")
		pg_query($connection,"COMMIT");
}

// keyword mapping.
if(strtolower($DatabaseType)=='oracle') 
	define("FROM_DUAL"," FROM DUAL ");
else
	define("FROM_DUAL"," ");

// DECODE and CASE-WHEN support
function db_case($array) 
{	global $DatabaseType;

	$counter=0;
	if(strtolower($DatabaseType)=="postgres") 
	{
		$array_count=count($array);
		$string = " CASE WHEN $array[0] =";
		$counter++;
		$arr_count = count($array);			
		for($i=1;$i<$arr_count;$i++) 
		{
			$value = $array[$i];
		
			if($value=="''" && substr($string,-1)=='=')
			{
				$value = ' IS NULL';
				$string = substr($string,0,-1);
			}
			elseif(substr($value,0,1)=='%' || substr($value,-2)=="%'")
			{
				$string = substr($string,0,-1).' LIKE ';
			}
			$string.="$value";
			if($counter==($array_count-2) && $array_count%2==0)
				$string.=" ELSE ";
			elseif($counter==($array_count-1))
				$string.=" END ";
			elseif($counter%2==0)
				$string.=" WHEN $array[0]=";
			elseif($counter%2==1)
				$string.=" THEN ";

			$counter++;
		}
	}
	else 
	{
		$string=" decode( ";
		foreach($array as $value)
			$string.="$value,";
		$string[strlen($string)-1]=")";
		$string.=" ";
	}
	return $string;
}

// String position.
function db_strpos($args) 
{	global $DatabaseType;

	if(strtolower($DatabaseType)=="postgres")
		$ret = 'strpos(';
	else
		$ret = 'instr(';

	foreach($args as $value)
		$ret .= $value . ',';
	$ret = substr($ret,0,-1) . ')';	
	
	return $ret;
}

// CONVERT VARCHAR TO NUMERIC
function db_to_number($text)
{	global $DatabaseType;

	if(strtolower($DatabaseType)=="postgres")
		return '('.$text.')::text::float::numeric';
	else
		return 'to_number('.$text.')';
}

// returns an array with the field names for the specified table as key with subkeys 
// of SIZE, TYPE, SCALE and NULL.  TYPE: varchar, numeric, etc. 
function db_properties($table)
{	global $DatabaseType,$DatabaseUsername;

	switch($DatabaseType)
	{
		case 'oracle':
			$sql="SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION,
				DATA_SCALE, NULLABLE, DATA_DEFAULT 
				FROM ALL_TAB_COLUMNS WHERE TABLE_NAME='".strtoupper($table)."'
				AND OWNER='".strtoupper($DatabaseUsername)."' ORDER BY COLUMN_ID";
			$result = DBQuery($sql);
			while($row=db_fetch_row($result))
			{
				if($row['DATA_TYPE']=='VARCHAR2')
				{
					$properties[$row['COLUMN_NAME']]['TYPE'] = "VARCHAR";
					$properties[$row['COLUMN_NAME']]['SIZE'] = $row['DATA_LENGTH'];
				}
				elseif($row['DATA_TYPE']=='NUMBER')
				{
					$properties[$row['COLUMN_NAME']]['TYPE'] = "NUMERIC";
					$properties[$row['COLUMN_NAME']]['SIZE'] = $row['DATA_PRECISION'];
					$properties[$row['COLUMN_NAME']]['SCALE'] = $row['DATA_SCALE'];
				}
				else
				{
					$properties[$row['COLUMN_NAME']]['TYPE'] = $row['DATA_TYPE'];
					$properties[$row['COLUMN_NAME']]['SIZE'] = $row['DATA_LENGTH'];
					$properties[$row['COLUMN_NAME']]['SCALE'] = $row['DATA_SCALE'];
				}
				$properties[$row['COLUMN_NAME']]['NULL'] = $row['NULLABLE'];
			}			
		break;
		case 'postgres':
			$sql = "SELECT a.attnum,a.attname AS field,t.typname AS type,
					a.attlen AS length,a.atttypmod AS lengthvar,
					a.attnotnull AS notnull 
				FROM pg_class c, pg_attribute a, pg_type t 
				WHERE c.relname = '".strtolower($table)."' 
					and a.attnum > 0 and a.attrelid = c.oid 
					and a.atttypid = t.oid ORDER BY a.attnum";
			$result = DBQuery($sql);
			while($row = db_fetch_row($result)) 
			{
				$properties[strtoupper($row['FIELD'])]['TYPE'] = strtoupper($row['TYPE']);
				if(strtoupper($row['TYPE'])=="NUMERIC")
				{
					$properties[strtoupper($row['FIELD'])]['SIZE'] = ($row['LENGTHVAR'] >> 16) & 0xffff;
					$properties[strtoupper($row['FIELD'])]['SCALE'] = ($row['LENGTHVAR'] -4) & 0xffff;
				}
				else
				{
					if($row['LENGTH']>0)
						$properties[strtoupper($row['FIELD'])]['SIZE'] = $row['LENGTH'];
					elseif($row['LENGTHVAR']>0)
						$properties[strtoupper($row['FIELD'])]['SIZE'] = $row['LENGTHVAR']-4;
				}
				if ($row['NOTNULL']=='t')
					$properties[strtoupper($row['FIELD'])]['NULL'] = "N";
				else
					$properties[strtoupper($row['FIELD'])]['NULL'] = "Y";
			}
		break;
		case 'mysql':
			$result = DBQuery("SHOW COLUMNS FROM $table");
			while($row = db_fetch_row($result))
			{
				$properties[strtoupper($row['FIELD'])]['TYPE'] = strtoupper($row['TYPE'],strpos($row['TYPE'],'('));
				if(!$pos = strpos($row['TYPE'],','))
					$pos = strpos($row['TYPE'],')');
				else
					$properties[strtoupper($row['FIELD'])]['SCALE'] = substr($row['TYPE'],$pos+1);

				$properties[strtoupper($row['FIELD'])]['SIZE'] = substr($row['TYPE'],strpos($row['TYPE'],'(')+1,$pos);
				
				if($row['NULL']!='')
					$properties[strtoupper($row['FIELD'])]['NULL'] = "Y";
				else
					$properties[strtoupper($row['FIELD'])]['NULL'] = "N";
			}
		break;
	}
	return $properties;
}

function db_show_error($sql,$failnote,$additional='')
{	global $FocusTitle,$FocusVersion,$FocusNotifyAddress;

	PopTable('header', 'We have a small problem ...');
	echo "
		<TABLE CELLSPACING=10 BORDER=0>
			<TD align=".ALIGN_RIGHT."><b>Date:</TD>
			<TD><pre>".date("m/d/Y h:i:s")."</pre></TD>
		</TR><TR>
			<TD align=".ALIGN_RIGHT."><b>Failure Notice:</b></TD>
			<TD><pre> $failnote </pre></TD>
		</TR><TR>
			<TD align=".ALIGN_RIGHT."><b>Additional Information:</b></TD>
			<TD>$additional</TD>
		</TR>
		</TABLE>";
	//Something you have asked the system to do has thrown a database error.  A system administrator has been notified, and the problem will be fixed as soon as possible.  It might be that changing the input parameters sent to this program will cause it to run properly.  Thanks for your patience.
	PopTable('footer');
	echo "<!-- SQL STATEMENT: \n\n $sql \n\n -->";
	Warehouse('footer');
	
	if ($FocusNotifyAddress) {
		$notify_addresses = $FocusNotifyAddress;
		$notify_subject = "Focus Database Error";
		$message = "
System: $FocusTitle
Version: $FocusVersion
Date: " . date("m/d/Y h:i:s") . "
Page: " . $_SERVER['PHP_SELF'] . ' ' . $_REQUEST['modname'] . "

Failure Notice:  $failnote
Additional Info: $additional

$sql

Request Array: 
" . ShowVar($_REQUEST, 'Y', 'N') ."
Session Array: 
" . ShowVar($_SESSION, 'Y', 'N');

		mail(  $notify_addresses
			 , $notify_subject
			 , $message
			);
	}

	die();
}

function DBEncode($value) {
	if (is_array($value)) {
		$return_value = "'||";
				 
		foreach ($value as $val) {
			if ($val) {
				$return_value .= str_replace("\'", "''"
					  , str_replace('``', '"', $val)) 
					  . '||';
			}
		}
		$return_value .= "'";
	}
	else {
		$return_value = "'" . str_replace("\'", "''"
				, str_replace('``', '"', $value)) . "'";
	}
	return $return_value;
}

function DBDecode($value) 
{
	$return_value = $value;
	
	if (substr($return_value, 0, 1) == "'"
	&&  substr($return_value, -1, 1) == "'"
	&& !( substr($return_value, -2, 1) == "'"
	 && substr($return_value, 1, 1) == "'" )
	){ // if it starts with ' and ends with ' remove them (unless it starts '' and ends '')
		$value = substr($return_value, 1, -1);
	}

	if (substr($return_value, 0, 2) == '||'
	&& substr($return_value, -2, 2) == '||'
	){ 
		$return_value =  explode("||", trim($return_value, '|') );
		foreach($return_value as & $val) {
			$val = str_replace("''", "'"
				, str_replace('"', '``', $val));
		}
		unset($val);
	}
	else {
		$return_value = str_replace("''", "'"
				, str_replace('"', '``', $return_value));
	}
	return $return_value;
}

// $safe_string = DBEscapeString($string).  Escapes single quotes by using two for every
// one.  Requires preg support in PHP.
function DBEscapeString($input)
{
   return preg_replace("/'/","''",$input);
}
?>
